/**
 * 
 */
package com.marianhello.bgloc.data.sqlite;

import java.util.ArrayList;
import java.util.Collection;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import com.marianhello.bgloc.data.LocationPackage;
import com.marianhello.bgloc.data.LocationPackageDAO;
import com.marianhello.bgloc.data.sqlite.LocationPackageContract.LocationPackageEntry;

/**
 * @author clu
 *
 */
public class SQLiteLocationPackageDAO implements LocationPackageDAO {
	private SQLiteDatabase db;
	
	public SQLiteLocationPackageDAO(Context context) {
		SQLiteOpenHelper helper = SQLiteOpenHelper.getHelper(context);
	    this.db = helper.getWritableDatabase();
	 }
	
	public SQLiteLocationPackageDAO(SQLiteDatabase db) {
	    this.db = db;
	}
	
	private Collection<LocationPackage> getLocationPackages(String whereClause, String[] whereArgs) {
	    Collection<LocationPackage> packages = new ArrayList<LocationPackage>();

	    String[] columns = {
	      LocationPackageEntry._ID,
	      LocationPackageEntry.COLUMN_NAME_PACKAGE_NUM,
	      LocationPackageEntry.COLUMN_NAME_PACKAGE_PAYLOAD,
	      LocationPackageEntry.COLUMN_NAME_TIME,
	      LocationPackageEntry.COLUMN_NAME_VALID
	    };

	    String groupBy = null;
	    String having = null;
	    String orderBy = LocationPackageEntry.COLUMN_NAME_TIME + " ASC";
	    Cursor cursor = null;

	    try {
	      cursor = db.query(
	    	  LocationPackageEntry.TABLE_NAME,  // The table to query
	          columns,                   // The columns to return
	          whereClause,               // The columns for the WHERE clause
	          whereArgs,                 // The values for the WHERE clause
	          groupBy,                   // don't group the rows
	          having,                    // don't filter by row groups
	          orderBy                    // The sort order
	      );
	      while (cursor.moveToNext()) {
	    	  packages.add(hydrate(cursor));
	      }
	    } finally {
	      if (cursor != null) {
	        cursor.close();
	      }
	    }

	    return packages;
	  }
	
	private LocationPackage hydrate(Cursor c) {
		LocationPackage lp = new LocationPackage();
		lp.setId(c.getLong(c.getColumnIndex(LocationPackageEntry._ID)));
		lp.setPackageNum(c.getInt(c.getColumnIndex(LocationPackageEntry.COLUMN_NAME_PACKAGE_NUM)));
		lp.setPackagePayload(c.getString(c.getColumnIndex(LocationPackageEntry.COLUMN_NAME_PACKAGE_PAYLOAD)));
		lp.setTime(c.getLong(c.getColumnIndex(LocationPackageEntry.COLUMN_NAME_TIME)));
		lp.setValid(c.getInt(c.getColumnIndex(LocationPackageEntry.COLUMN_NAME_VALID)) != 0);

	    return lp;
	  }
	
	/* (non-Javadoc)
	 * @see com.marianhello.bgloc.data.LocationPackageDAO#getAll()
	 */
	@Override
	public Collection<LocationPackage> getAll() {
		String whereClause = LocationPackageEntry.COLUMN_NAME_VALID + " = ?";
	    String[] whereArgs = { "1" };

	    return getLocationPackages(whereClause, whereArgs);
	}

	/* (non-Javadoc)
	 * @see com.marianhello.bgloc.data.LocationPackageDAO#persist(com.marianhello.bgloc.data.BackgroundLocation)
	 */
	@Override
	public Long persist(LocationPackage lp) {
		ContentValues values = getContentValues(lp);
	    long rowId = db.insertOrThrow(LocationPackageEntry.TABLE_NAME, LocationPackageEntry.COLUMN_NAME_NULLABLE, values);

	    return rowId;
	}
	
	public Long persistLocationWithLimit(LocationPackage lp, Integer maxRows) {
	    Long rowId = null;
//	    String sql = null;
//	    Boolean shouldVacuum = false;

	    long rowCount = DatabaseUtils.queryNumEntries(db, LocationPackageEntry.TABLE_NAME);

	    if (rowCount < maxRows) {
	    	return persist(lp);
	    }

	    db.beginTransactionNonExclusive();

//	    if (rowCount > maxRows) {
//	      sql = new StringBuilder("DELETE FROM ")
//	              .append(LocationPackageEntry.TABLE_NAME)
//	              .append(" WHERE ").append(LocationPackageEntry._ID)
//	              .append(" IN (SELECT ").append(LocationPackageEntry._ID)
//	              .append(" FROM ").append(LocationPackageEntry.TABLE_NAME)
//	              .append(" ORDER BY ").append(LocationPackageEntry.COLUMN_NAME_TIME)
//	              .append(" LIMIT ?)")
//	              .toString();
//	      db.execSQL(sql, new Object[] {(rowCount - maxRows)});
//	      shouldVacuum = true;
//	    }

//	    sql = new StringBuilder("UPDATE ")
//	            .append(LocationPackageEntry.TABLE_NAME).append(" SET ")
//	            .append(LocationPackageEntry.COLUMN_NAME_TIME).append("= ?,")
//	            .append(LocationPackageEntry.COLUMN_NAME_PACKAGE_NUM).append("= ?,")
//	            .append(LocationPackageEntry.COLUMN_NAME_PACKAGE_PAYLOAD).append("= ?,")
//	            .append(LocationPackageEntry.COLUMN_NAME_VALID).append("= ?")
//	            .append(" WHERE ").append(LocationPackageEntry.COLUMN_NAME_TIME)
//	            .append("= (SELECT min(").append(LocationPackageEntry.COLUMN_NAME_TIME).append(") FROM ")
//	            .append(LocationPackageEntry.TABLE_NAME).append(")")
//	            .toString();
//	    db.execSQL(sql, new Object[] {
//	            lp.getTime(),
//	            lp.getPackageNum(),
//	            lp.getPackagePayload(),
//				lp.isValid() ? 1 : 0
//	    });

//	    rowId = getLastInsertRowId(db);
	    rowId = updateWith(lp);
	    db.setTransactionSuccessful();
	    db.endTransaction();

//	    if (shouldVacuum) { db.execSQL("VACUUM"); }

	    return rowId;
	}
	
	public Long updateWith(LocationPackage lp) {
		String[] columns = {
			LocationPackageEntry._ID,
			LocationPackageEntry.COLUMN_NAME_PACKAGE_NUM,
			LocationPackageEntry.COLUMN_NAME_PACKAGE_PAYLOAD,
			LocationPackageEntry.COLUMN_NAME_TIME,
			LocationPackageEntry.COLUMN_NAME_VALID
		};

		String groupBy = null;
		String having = null;
		String whereClause = null;
	    String[] whereArgs = null;
		String orderBy = LocationPackageEntry.COLUMN_NAME_TIME + " ASC";
		String limit = "1";
		Cursor cursor = null;
		
		ContentValues values = null;
		long rowId = -1L;
		
		try {
			cursor = db.query(
				LocationPackageEntry.TABLE_NAME,  // The table to query
				columns,                   // The columns to return
				whereClause,               // The columns for the WHERE clause
		        whereArgs,                 // The values for the WHERE clause
		        groupBy,                   // don't group the rows
		        having,                    // don't filter by row groups
		        orderBy,                   // The sort order
		        limit
		    );
		    while (cursor.moveToNext()) {
		    	values = new ContentValues();
		    	values.put(LocationPackageEntry._ID, cursor.getLong(cursor.getColumnIndex(LocationPackageEntry._ID)));
		    	values.put(LocationPackageEntry.COLUMN_NAME_PACKAGE_NUM, lp.getPackageNum());
		    	values.put(LocationPackageEntry.COLUMN_NAME_PACKAGE_PAYLOAD, lp.getPackagePayload());
		    	values.put(LocationPackageEntry.COLUMN_NAME_TIME, lp.getTime());
		    	values.put(LocationPackageEntry.COLUMN_NAME_VALID, lp.isValid() ? 1 : 0);
		    	
		    }
		} finally {
			if (cursor != null) {
				cursor.close();
		    }
		}
		
		if (values != null) {
			rowId = db.replaceOrThrow(LocationPackageEntry.TABLE_NAME, LocationPackageEntry.COLUMN_NAME_NULLABLE, values);
		}
		
	    return rowId;
	}
	
	private ContentValues getContentValues(LocationPackage lp) {
	    ContentValues values = new ContentValues();
	    values.put(LocationPackageEntry.COLUMN_NAME_TIME, lp.getTime());
	    values.put(LocationPackageEntry.COLUMN_NAME_PACKAGE_NUM, lp.getPackageNum());
	    values.put(LocationPackageEntry.COLUMN_NAME_PACKAGE_PAYLOAD, lp.getPackagePayload());
	    values.put(LocationPackageEntry.COLUMN_NAME_VALID, lp.isValid() ? 1 : 0);

	    return values;
	}
	
	public long getLastInsertRowId(SQLiteDatabase db) {
	    Cursor cur = db.rawQuery("SELECT last_insert_rowid()", null);
	    cur.moveToFirst();
	    long id = cur.getLong(0);
	    cur.close();
	    return id;
	}
	
	/* (non-Javadoc)
	 * @see com.marianhello.bgloc.data.LocationPackageDAO#delete()
	 */
	@Override
	public void delete(Long id) {
	    ContentValues values = new ContentValues();
	    values.put(LocationPackageEntry.COLUMN_NAME_VALID, 0);

	    String whereClause = LocationPackageEntry._ID + " = ?";
	    String[] whereArgs = { String.valueOf(id) };

	    db.update(LocationPackageEntry.TABLE_NAME, values, whereClause, whereArgs);
	}

	/* (non-Javadoc)
	 * @see com.marianhello.bgloc.data.LocationPackageDAO#deleteAll()
	 */
	@Override
	public void deleteAll() {
		ContentValues values = new ContentValues();
		values.put(LocationPackageEntry.COLUMN_NAME_VALID, 0);

		db.update(LocationPackageEntry.TABLE_NAME, values, null, null);
	}

	@Override
	public void clear() {
		db.beginTransactionNonExclusive();
	    db.delete(LocationPackageEntry.TABLE_NAME, null, null);
	    db.setTransactionSuccessful();
	    db.endTransaction();
	}

}
